Home:  Products:  Font Tools:  Bar Code Printing Macros & VBA Functions for Microsoft Excel and Access:

Microsoft Office Bar Code Macros & VBA Functions
for Excel, Access and Word on Windows or Macintosh Systems

Our barcode macros used in Excel to format a Code 128 barcode font.

This website offers bar code macros and functions for the intermediate to advanced Microsoft® Office user. If you are looking for the easiest method of implementing barcodes in Office, please visit our OfficeBarcode.com site.

Index:

Additional Font Tools: Windows DLL | Barcode Printing Application | Crystal Reports UFL | LotusScript |  Font Tools Index

User Comments

"The IDAutomation.com Code 128 Font and VBA macros provided enabled us to integrate barcoding into a Microsoft Excel spreadsheet that works cross-platform between Classic Macintosh, OS-X and Microsoft Windows operating systems." - Charles Daneri, Objective Systems, LLC (computer consulting firm), Baltimore, MD.

Overview:

To sign up for monthly updates about new products or upgrades, please click here.Our macros will generate check digits, add the start and stop characters and format the return string for one of our barcode fonts. The macros can be easily integrated into your application for automation purposes and you don't have to be a programmer to use them - they are easy enough for intermediate and advanced users of Excel or Access. The macros consist of a VBA module with many functions which are defined here. The macros are free to use as long as you are using them with one of our licensed barcode fonts. They are compatible with Microsoft Office 97, Office 2000, Office XP and Office 2003 running on Windows® or Macintosh systems. The unique encoding in our fonts and macros allow cross-platform support between Macintosh and Windows systems. For example, the same Microsoft Excel spreadsheet can be used on Windows and MAC computers that have our font installed.

If this product does not completely meet your needs, you may wish to evaluate other solutions we offer such as our ActiveX Controls or Barcode Label Software. Our ActiveX Controls are usually easier to use in Access than barcode fonts. They also work well with Visual Basic 6. You may also wish to consider our Barcode Label Software, which is the easiest software to use if you are printing barcodes on labels.
 

Tutorial for Using VBA Barcode Macro Functions in Excel:

After you have downloaded and extracted the files, open the sample Excel spreadsheet provided. The Excel spreadsheet contains a few examples for various barcode types. A VBA module is included in the spreadsheet that contains custom functions and macros that format data to our barcode fonts. You should refer to this sample spreadsheet if you need help creating barcodes.

How to create a barcode in Excel:

  1. Most recent versions of Excel require a medium security setting to run the macros. Open Excel and set the security level to medium by choosing Tools - Macro - Security. When the document is re-opened, choose “enable macros” if prompted.
  2. If you are using your own spreadsheet and not the sample we provide, you must first import the macros into your spreadsheet as described here.
  3. Create an area in your spreadsheet for the barcode.
  4. Size the column to make sure it is wide enough to contain the entire barcode.
  5. Format the column so that any text appearing in it will be centered. This is necessary to create the white space (often called the quiet zone) before and after the barcode.
  6. Enter the formula in this cell that will format the data to the barcode font. If you are unsure of which function or barcode to use, we recommend using our Code 128 fonts with the =Code128(B10) formula where B10 refers to the cell location of the data that is to be formatted and encoded in the barcode. A complete list of available functions is provided here.
  7. Examine the spreadsheet to make sure the data is being properly formatted to the barcode font. You should see that some strange characters have been appended to the beginning and ending of the data being encoded - this is normal. Sometimes the data has to be formatted so much it appears to be scrambled. This is normal for Code 128 and Interleaved 2 of 5 when numbers need to be compressed within the barcode.
    Excel barcode formula example.
  8. After you verify the text in the cell is being formatted, select the appropriate font and point size for the cell. In this example, we select the IDAutomationC128XS font to create a Code 128 barcode. If you are unsure of the point size to use, we recommend 12 points. The XS and S sizes of our fonts in Code 128 and Code 39 are specifically designed to format correctly in Excel. Larger fonts will not usually format correctly in the cell.
    Selecting the barcode font to use in Microsoft Excel. 
  9. Be sure the column is wide enough to contain the entire barcode and some white space before and after the barcode.

For information about other methods of barcoding in Excel, please refer to our Microsoft Excel Barcode FAQ and Tutorial.

Adding barcodes to an entire column in Excel

This may be necessary if you have a large column of data that needs to be barcoded and you do not want to copy and paste the barcode font formula into each cell. In this example, we create Code 128 barcodes in the Excel column B from the data in column A. This example assumes you have installed the Code 128 Font Package and imported the VBA module into your spreadsheet.

  1. Enter the formula in one cell to format the data to the barcode font as explained in the Excel tutorial:
  2. Select that cell and choose Edit - Copy.
  3. Highlight an entire column by selecting the gray square labeled "B" at the top of the spreadsheet and choose Edit - Paste. The formulas may take some time to re-calculate.
     
  4. Change the font of the column to be the appropriate barcode font. In this example, with the entire column that contains the formula selected, we choose the IDAutomationC128S font. Your barcodes will appear in the entire column.
     
Importing and Exporting the VBA code and modules in Excel

The barcode functions and formulas reside inside the Excel file as a custom module. To use these barcode functions in your Excel file, you must first import the IDAutomationVBA module into your spreadsheet. If the IDAutomationVBA.bas file is not available for import, you may export it from the sample spreadsheet provided.

Tutorial for Using VBA Barcode Macro Functions in Access:

Sample reports provided in Access 2000

After you have downloaded and extracted the files, open the sample Access database provided. The sample database contains one table, some sample reports and one module. The module contains the custom VBA code, functions and macros. You should refer to this sample database if you need help creating barcodes.

How to create a barcode in an Access Report:

  1. If you are using your own database and not the sample we provide, you must first import the macros into your database as described here.
  2. Open a report in design mode.
  3. Create a text box where you want the barcode to appear.
  4. To format the data to the barcode in a text box, you need to enter the formula =function([table.field]) in the Control Source property of the text box. Be sure to enter the table and the field in the Control Source property. If you are unsure of which function to use, we recommend using our Code 128 fonts with the =[Code128(table.field)] function. A complete list of available functions is provided here. Below is an example of using the SSCC-18 function in the Control Source property.
     control source in Access 2000 for SSCC-18 barcode
  5. Run the report to make sure the data is being pulled from the fields and properly formatted to the barcode font. You should see that some strange characters have been appended to the beginning and ending of the data from the fields - this is normal. Sometimes the data has to be formatted so much it appears to be scrambled. This is normal for Code 128 and Interleaved 2 of 5 when numbers need to be compressed within the barcode.
  6. Change the font of the formula field to be the appropriate barcode font and set the point size. This is a very important step; if you do not do this, no barcodes can be displayed. If you are unsure of the point size to use, we recommend 12 points.

For information about other methods of barcoding in Access, please refer to our Microsoft Access Barcode FAQ and Tutorial.

Importing and Exporting the Macros and VBA into Access

The VBA code and functions reside inside the access database as a module. This makes it easy to deploy barcode applications for users that already use Access. To use these functions in another Access file, you must copy the module to another database or first export the module and then import it into the other database. 

Tutorial for Using barcodes in Microsoft Word:

Microsoft Word does not have the capability to call VBA functions from within a document or mail-merge like Excel and Access can. Therefore, to barcode in MS Word, it is necessary to use Excel as the data source for the mail merge. The field used for the data source in Word should point to the column in Excel where the formula has already been applied. We suggest using the last column of your spreadsheet in order to avoid any merging problems. To setup your Excel spreadsheet with the macros, please see the Excel Tutorial.

  1. After you have downloaded and extracted the files, open "MS Word Mail Merge.doc". You may get a message stating that the data source cannot be found. If this happens, choose "Data Source for Word Mail Merge.xls" as the data source. If asked to enable Macros, choose Yes.
  2. In Excel, you can view the formula in the cells listed under the "Barcode" column. It is this formula that retrieves the data to encode and passes it to the appropriate barcode function. The function will then format the data to encode in a string formatted to the barcode. This column is then passed to Word when the mail merge is started. Then, the barcode font must be selected for the field in MS Word (in this case it is «Barcode») and it MUST be the same symbology.
  3. The barcode will not look correct and will not scan until the mail merge is performed because before the mail merge, only the field is displayed. Fully functional versions of our fonts must be installed to create a proper barcode.
  4. The VBA code and functions reside inside the Excel file, not the MS Word file. This makes it easy to deploy barcode applications for users that already use Word and Excel. To setup your Excel spreadsheet with the macros, please see the Excel Tutorial.
If you are trying to embed your True Type barcode font into a Word document, please visit the following page to assist you. You may only embed fonts in pdf documents on the public Internet or for distribution outside of your company if you purchase a Developer License for the font and you do not encourage users to extract the properties of the embedded fonts. If the pdf documents will be internal to your company, you will only need to purchase the correct number of Single User Licenses for the number of users who will be creating and viewing the pdf documents.
 
The Following Macros are Available for MS Office:

For all functions listed below, the DataToEncode is the string data type. Other data types, such as numbers or dates, may need to be converted to the string data type to be properly encoded. Additional parameters are available for some functions and are optional. For example, Code128("123456",0,True). For all number symbologies, such as POSTNET and UPC, you may input dashes or spaces for easier readability and they will be filtered out by the function before the barcode is generated. For UPC-A, UPC-E & EAN-13, the +2 and +5 add-on codes may be created by adding the data to the end of the string.

Function Notes
Code128(DataToEncode,
Optional ReturnType,
Optional ApplyTilde)		 If you are not sure which Code 128 set is for your application, then use this one. This is a "Code 128 Auto" function that will automatically encode any DataToEncode from ASCII 0 to ASCII 127. It will automatically switch to character set C for numbers also. Data may be easily encoded without any options. For example, the formula Code128("123456") will encode the numbers 123456.

It may be necessary to use the optional ReturnType and ApplyTilde for special purposes.

  • If ApplyTilde is set to True, the tilde will be processed as described here. ApplyTilde is False by default.
  • To encode alpha-numeric UCC/EAN-128, ASCII 202 or character Ê is entered as the FNC1 before each AI. For example, the UCC number of (8100)712345(21)12WH5678 should be entered as: Ê8100712345Ê2112WH5678.
    More information about AIs and UCC/EAN-128 is located here.
    (DataToEncode, 0) formats barcode output string to the Code 128 barcode fonts.
    (DataToEncode, 0, True) formats barcode output string for the Code 128 barcode fonts with ApplyTilde enabled.
    (DataToEncode, 1) returns the human readable text.
    (DataToEncode, 2) returns only the check digit.
Code128a(DataToEncode) Caution: Entering a lower case character will create a function. Use the character values 64 through 95 from set A to print characters not on the keyboard; the scanner will recognize these functions as from set A. Formats output to the Code 128 barcode fonts.
Code128b(DataToEncode) Returns codes formatted to the Code 128 character set B. Formats output to the Code 128 bar code fonts.
Code128c(DataToEncode,
Optional ReturnType)		 This code128 function "interleaves" numbers into pairs for high density.
(DataToEncode, 0) formats output to the Code 128 barcode fonts.
(DataToEncode, 1) returns the human readable text with the check digit included.
(DataToEncode, 2) returns only the check digit.
I2of5(DataToEncode) This function "interleaves" numbers into pairs for high density without check digits and formats the return string to the Interleaved 2 of 5 font.
I2of5Mod10(DataToEncode,
Optional ReturnType)		 (DataToEncode, 0) performs the mod10 checksum calculation for increased accuracy and formats the return string to the Interleaved 2 of 5 font. MOD 10 checksums are required by USPS for special services and for SSC-14 when using Interleaved 2 of 5 for that purpose.
(DataToEncode, 1) returns the human readable data with the MOD10 check digit included.
(DataToEncode, 2) returns the MOD10 check digit.
Code39(DataToEncode) Formats the output to print using Code 39 fonts.
Code39Mod43(DataToEncode,
Optional ReturnType)		 (DataToEncode, 0) performs the mod43 checksum calculation for increased accuracy and then formats the output to print using Code 39 fonts. The mod43 checksum is usually required for LOGMARS and HIBC applications.
(DataToEncode, 1) returns the human readable data with the check digit included.
(DataToEncode, 2) returns only the check digit.
Codabar(DataToEncode) Formats the output to print using Codabar fonts.
UCC128(DataToEncode) This symbology option encodes an even number of number digits and includes the FNC1 character in set C as required. Use Code 128 Auto to encode additional FNC1 codes or data containing text or odd numbers. Use the UCC128 function only for UCC-128 applications where the input data is an even number such as in SSCC-18 and SCC-14 barcodes. For example, to encode an SSCC-18 barcode, you would enter 00000123455555555558 as the data input. For more information, please visit here. Formats output to the Code 128 barcode fonts.
SCC14(DataToEncode,
Optional ReturnType)		 SCC14(DataToEncode, 0) generates the MOD10 required and then formats the output to print EAN-14 and SCC-14 using Code 128 fonts. The input for Data must be a 13 to 17 digit number, however, only 13 are needed. The required application identifier of (01) is automatically added.
SCC14(DataToEncode, 1) returns the human readable data for SCC14 / EAN14.
SCC14(DataToEncode, 2) returns the MOD10 check digit.
SSCC18(DataToEncode,
Optional ReturnType)		 SCC18(DataToEncode, 0) generates the MOD10 required for SSCC-18 and then formats the output to print SSCC-18 using Code 128 fonts. The input for Data must be a 17 to 21 digit number, however, only 17 are needed. The required application identifier of (00) is automatically added.
SCC18(DataToEncode, 1) returns the human readable data for SCC-18.
SCC18(DataToEncode, 2) returns the MOD10 check digit.
Postnet(DataToEncode,
Optional ReturnType)		 Enter a single string of Zip, Zip + 4 or Zip + 4 + Delivery Point. The DataToEncode must be a number and can include dashes and spaces.
(DataToEncode, 0) formats output to the POSTNET barcode fonts.
(DataToEncode, 1) returns the human readable data with the check digit included.
(DataToEncode, 2) returns only the check digit.
USPS_EAN128 (DataToEncode,
Optional ReturnType)		 Used for 22 digit USPS special services labels such as delivery confirmation in EAN128. This is used with our Code 128 fonts. This new EAN128 format is mandatory as of January 10, 2004 according to the USPS Delivery Confirmation Service defined in the September 2002 version of Publication 91. Enter a 19 or 20 digit number; only the first 19 are used. This number is made up of the following:  2 digit service code + 9 digit customer ID + 8 digit sequential package ID + MOD 10 check digit that can be calculated by this function if excluded. In this function, the application identifier of 91 is automatically added for you. Other USPS EAN128 barcode types must be created by calling Code128() with the appropriate ASCII 202 and AIs included (as documented at our Code 128 FAQ). You may also refer to the USPS portion of the Code 128 FAQ.
(DataToEncode, 0) formats output to the Code 128 barcode fonts.
(DataToEncode, 1) returns the human readable data with the check digit included.
(DataToEncode, 2) returns only the check digit.
MOD10(DataToEncode) Returns the MOD 10 check digit for a given string of numbers according to the UCC/EAN method.
RM4SCC(DataToEncode) Formats the output to print using RM4SCC fonts. Checksum calculated according to the mailsort customer barcoding specification.
MSI(DataToEncode,
Optional ReturnType)		 (DataToEncode, 0) formats output to the MSI barcode fonts.
(DataToEncode, 1) returns the human readable data with the check digit included.
(DataToEncode, 2) returns only the check digit.
Code11(DataToEncode) Only the "C" check digit is calculated which is the standard when encoding 10 digits or less. Formats output to the Code11 barcode fonts.
UPCa(DataToEncode) DataToEncode is a UPC-A number string of 11, 12, 13, 14, 16 or 17 digits with or without a check digit, add-ons are supported.  Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing all zeros.
UPCe(DataToEncode) DataToEncode is a UPC-A number string of 11, 12, 13, 14, 16 or 17 digits with or without a check digit, add-ons are supported. The purpose of this function is to print the UPC-E barcode from a UPC-A barcode that can be compressed. Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing "00005000000".
EAN13(DataToEncode) DataToEncode is a number string of 12, 13, 14, 15, 17 or 18 digits with or without a check digit, add-ons are supported. Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing all zeros.
EAN8(DataToEncode) DataToEncode is a number string of 7 or 8 characters (EAN-8 without the check digit). Formats output to the UPC/EAN barcode font. Entering incorrect data will create a barcode containing all zeros.

ApplyTilde - In Code 128 auto, if the ApplyTilde option is set to True, the tilde will be processed and allow the following encoding options:

View our product index to obtain a list of all products we offer.

 

 Product Links: [Barcode Fonts | Barcode Components | Barcode Label Software | Barcode Scanners]

To sign up for monthly updates about new products or upgrades, please click here.Copyright © 2000-2005 IDAutomation.com, Inc. IDAutomation and BizFonts are registered trademarks of IDAutomation.com, Inc. All other trademarks mentioned are the property of their respective owners.
 

Over 70% of Fortune 100 companies use our products to automate their businesses.